﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OleDb;
using System.Data.Odbc;


public class FeedbackForm
{
    private String courseID;
    private String courseName;
    private String courseInstructor;
    private String courseInstructorEmailID;
    private List<String> coCourseInstructor;
    private List<String> coCourseInstructorEmailID;
    private List<String> TA;
    private List<String> TAEmailID;
    private DateTime startDate;
    private DateTime endDate;
    private DateTime summDate;
    private int feedback_ID;
    private String xlsFileName;

    public FeedbackForm()
    {

    }

    public int createNewForm()
    {


        try
        {
            string sql = "SELECT COUNT(*) FROM course_feedback";
            DbUtilities dbUtil = new DbUtilities(sql);

            feedback_ID = Int32.Parse(dbUtil.retrieveCount());
            feedback_ID++;

            sql = "INSERT INTO course_feedback VALUES('"
                + feedback_ID + "','"
                + courseID + "','"
                + courseName + "','"
                + startDate.ToString("yyyy-MM-dd") + "','"
                + endDate.ToString("yyyy-MM-dd") + "','"
                + summDate.ToString("yyyy-MM-dd") + "')";

            dbUtil = new DbUtilities(sql);
            dbUtil.writeInfo();

            sql = "INSERT INTO instructor VALUES('"
                + courseInstructor + "','"
                + courseInstructorEmailID + "','"
                + feedback_ID + "')";

            dbUtil = new DbUtilities(sql);
            dbUtil.writeInfo();

            for (int i = 0; i < coCourseInstructor.Count; i++)
            {
                sql = "INSERT INTO co_instructor VALUES('"
                    + coCourseInstructor.ElementAt(i) + "','"
                    + coCourseInstructorEmailID.ElementAt(i) + "','"
                    + feedback_ID + "')";

                dbUtil = new DbUtilities(sql);
                dbUtil.writeInfo();
            }


            for (int i = 0; i < TA.Count; i++)
            {
                sql = "INSERT INTO ta VALUES('"
                    + TA.ElementAt(i) + "','"
                    + TAEmailID.ElementAt(i) + "','"
                    + feedback_ID + "')";

                dbUtil = new DbUtilities(sql);
                dbUtil.writeInfo();
            }

            OleDbConnection objConn;
            String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=D://5th Sem//SE//Coding//XLS//" + xlsFileName + ";" + "Extended Properties=Excel 8.0;";
            objConn = new OleDbConnection(connectionString);
            objConn.Open();
            String strConString = "SELECT * FROM [Sheet1$]";
            OleDbCommand objCmdSelect = new OleDbCommand(strConString, objConn);
            // Create new OleDbDataAdapter that is used to build a DataSet 
            // based on the preceding SQL SELECT statement. 
            OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
            // Pass the Select command to the adapter. 
            objAdapter1.SelectCommand = objCmdSelect;
            // Create new DataSet to hold information from the worksheet. 
            DataSet objDataset1 = new DataSet();
            // Fill the DataSet with the information from the worksheet. 
            objAdapter1.Fill(objDataset1, "ExcelData");


            for (int i = 0; i < objDataset1.Tables[0].Rows.Count; i++)
            {
                //Response.Write("Data: " + objDataset1.Tables[0].Rows[i].ItemArray[0].ToString() + " " + objDataset1.Tables[0].Rows[i].ItemArray[1].ToString() + " ");
                sql = "INSERT INTO student VALUES('"
                    + objDataset1.Tables[0].Rows[i].ItemArray[0].ToString() + "','"
                    + objDataset1.Tables[0].Rows[i].ItemArray[1].ToString() + "','"
                    + objDataset1.Tables[0].Rows[i].ItemArray[2].ToString() + "','"
                    + feedback_ID + "','"
                    + "0')";

                dbUtil = new DbUtilities(sql);
                dbUtil.writeInfo();
            }

            objConn.Close();

        }
        catch (Exception ex)
        {
            return 2;
        }


        Mailer mailNotify = new Mailer(feedback_ID);
        if (mailNotify.mailCreateNotification() != true)
            return 3;

        else
            return 1;

    }




    public int checkXLS(String fileName)
    {
        try
        {
            xlsFileName = fileName;
            OleDbConnection objConn;
            String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=D://5th Sem//SE//Coding//XLS//" + xlsFileName + ";" + "Extended Properties=Excel 8.0;";
            objConn = new OleDbConnection(connectionString);
            objConn.Open();
            String strConString = "SELECT * FROM [Sheet1$]";
            OleDbCommand objCmdSelect = new OleDbCommand(strConString, objConn);
            // Create new OleDbDataAdapter that is used to build a DataSet 
            // based on the preceding SQL SELECT statement. 
            OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
            // Pass the Select command to the adapter. 
            objAdapter1.SelectCommand = objCmdSelect;
            // Create new DataSet to hold information from the worksheet. 
            DataSet objDataset1 = new DataSet();
            // Fill the DataSet with the information from the worksheet. 
            objAdapter1.Fill(objDataset1, "ExcelData");

            if (objDataset1.Tables[0].Columns.Count != 3)
            {
                objConn.Close();
                return 2;
            }


            else if (objDataset1.Tables[0].Rows.Count <= 1)
            {
                objConn.Close();
                return 3;
            }

            else
            {
                objConn.Close();
                return 1;
            }
        }
        catch (Exception ex)
        {
            return 4;

        }
    }

    public int checkNumberResponse(String feedback)
    {
        try
        {
            string sql = "SELECT COUNT(*) FROM response WHERE Feedback_ID = " + feedback;
            DbUtilities getCount = new DbUtilities(sql);
            String numberResponses = getCount.retrieveCount();
            return Int32.Parse(numberResponses);
        }
        catch (Exception ex)
        {
            return -1;
        }

    }


    public void setCourseID(String courseID)
    {
        this.courseID = courseID;
    }

    public void setCourseName(String courseName)
    {
        this.courseName = courseName;
    }

    public void setCourseInstructor(String courseInstructor, String courseInstructorEmailID)
    {
        this.courseInstructor = courseInstructor;
        this.courseInstructorEmailID = courseInstructorEmailID;
    }

    public void setCoCourseInstructor(List<String> coCourseInstructor, List<String> coCourseInstructorEmail)
    {
        this.coCourseInstructor = coCourseInstructor;
        this.coCourseInstructorEmailID = coCourseInstructorEmail;
    }


    public void setTA(List<String> TA, List<String> TAEmailID)
    {
        this.TA = TA;
        this.TAEmailID = TAEmailID;
    }

    public void setDates(DateTime startDate, DateTime endDate, DateTime summDate)
    {
        this.startDate = startDate;
        this.endDate = endDate;
        this.summDate = summDate;
    }

}